package com.yaodian100.ec.product.service.impl;

import java.math.BigDecimal;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import com.yaodian100.core.common.service.BaseManager;
import com.yaodian100.core.common.service.impl.DomainServiceImpl;
import com.yaodian100.ec.category.entity.Category;
import com.yaodian100.ec.product.entity.ProdCatRC;
import com.yaodian100.ec.product.service.ProductRCService;

/**
 * 馆长推荐
 * 
 * @author aaron.chueh
 * 
 */
public class ProductRCServiceImpl extends DomainServiceImpl<ProdCatRC> implements ProductRCService {
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Resource
	private BaseManager baseManager;

	@Transactional(readOnly = false)
	public int getProductCount(String categoryId, String keyword) {
		String keywordSql = "";
		if (keyword != null && keyword.length() > 0) {
			keywordSql = "and (a.product_id like ? or a.product_name like ?) ";
		}
		String countSql = "select distinct count(*) from EC_VIEW_CATEGORY_PRODUCT a,(select * from "
				+ "(select cat2.product_id, cat2.category_id, cat2.show_seq as s1,"
				+ "cat1.show_seq as s2,rank()over(partition by cat2.product_id "
				+ "order by cat2.show_seq,cat1.show_seq ) as rank  " + "from ec_category_main cat1, ec_product_category cat2 "
				+ "where cat1.category_id = cat2.category_id  " + "and cat1.category_path = cat2.category_path "
				+ "and cat1.is_delete  = 'N'  " + "and (cat1.category_path like ? or cat1.category_id=?)"
				+ "order by cat2.product_id desc) " + "where rank =1) b " + "where a.product_id = b.product_id and "
				+ "substr(a. v_uk,  (InStr(a.v_uk,'_')) +1 ) = b.category_id and "
				+ "(a.category_path like ? or a.category_id=?) " + "and (a.is_delete = 'N' or a.is_delete = 'Q') " + keywordSql;

		String tempZ = this.getTempZ(categoryId);
		if (keyword == null) {
			keyword = "";
		}
		if (keyword != null && keyword.length() > 0) {
			keyword = "%" + keyword + "%";
		}

		int totalCount = 0;
		if (keyword != null && keyword.length() > 0) {
			totalCount = jdbcTemplate.queryForInt(countSql, tempZ, categoryId, tempZ, categoryId, keyword, keyword);
		} else {
			totalCount = jdbcTemplate.queryForInt(countSql, tempZ, categoryId, tempZ, categoryId);
		}

		return totalCount;
	}

	@Transactional(readOnly = false)
	public List getProductList(String categoryId, String keyword, int endRow, int startRow) {
		String keywordSql = "";
		if (keyword != null && keyword.length() > 0) {
			keywordSql = "and (a.product_id like ? or a.product_name like ?) ";
		}
		String sql = "SELECT * FROM(" + "SELECT RES.* ,ROWNUM NUM FROM ("
				+ "select distinct a.* from EC_VIEW_CATEGORY_PRODUCT a,(select * from "
				+ "(select cat2.product_id, cat2.category_id, cat2.show_seq as s1,"
				+ "cat1.show_seq as s2,rank()over(partition by cat2.product_id "
				+ "order by cat2.show_seq,cat1.show_seq ) as rank  " + "from ec_category_main cat1, ec_product_category cat2 "
				+ "where cat1.category_id = cat2.category_id  " + "and cat1.category_path = cat2.category_path "
				+ "and cat1.is_delete  = 'N'  " + "and (cat1.category_path like ? or cat1.category_id=?) "
				+ "order by cat2.product_id desc) " + "where rank =1) b " + "where a.product_id = b.product_id and "
				+ "substr(a. v_uk,  (InStr(a.v_uk,'_')) +1 ) = b.category_id and "
				+ "(a.category_path like ? or a.category_id=?) " + "and (a.is_delete = 'N' or a.is_delete = 'Q') " + keywordSql
				+ " order by  a.show_Seq asc,a.product_id desc" + ") RES " + "WHERE ROWNUM<=? " + ") " + "WHERE NUM>=?";

		String tempZ = this.getTempZ(categoryId);
		if (keyword == null) {
			keyword = "";
		} else {
			keyword = "%" + keyword + "%";
		}
		List productList = null;
		if (keyword != null && keyword.length() > 0) {
			productList = jdbcTemplate.queryForList(sql, tempZ, categoryId, tempZ, categoryId, keyword, keyword, endRow,
					startRow);
		} else {
			productList = jdbcTemplate.queryForList(sql, tempZ, categoryId, tempZ, categoryId, endRow, startRow);
		}

		return productList;
	}

	@Transactional(readOnly = false)
	public void delProductsRC(String[] ids) {
		for (int i = 0; i < ids.length; i++) {
			if (ids[i] != null && !ids[i].equals("")) {
				ProdCatRC prodCatRC = this.get(ids[i]);
				this.delete(prodCatRC);
			}
		}
	}

	@Transactional(readOnly = false)
	public void dftProductsRC(List<ProdCatRC> prodCatRCList) {
		for (int i = 0; i < prodCatRCList.size(); i++) {
			ProdCatRC prodCatRC = prodCatRCList.get(i);
			if (prodCatRC.getId().equals("")) {
				this.save(prodCatRC);
			} else {
				this.update(prodCatRC);
			}
		}
	}

	@Transactional(readOnly = false)
	public void saveProductsRC(List<ProdCatRC> prodCatRCList) {
		Collections.sort(prodCatRCList);
		for (int i = 0; i < prodCatRCList.size(); i++) {
			ProdCatRC prodCatRC = prodCatRCList.get(i);
			prodCatRC.setSortOrder((i + 1) + "");
			prodCatRC.setSortWeight(null);

			if (prodCatRC.getId().equals("")) {
				this.save(prodCatRC);
			} else {
				this.update(prodCatRC);
			}
		}
	}

	public boolean checkCateProdRC(ProdCatRC prodCatRC) {
		String checkSql = "select distinct a.* from EC_VIEW_CATEGORY_PRODUCT a,(select * from "
				+ "(select cat2.product_id, cat2.category_id, cat2.show_seq as s1,"
				+ "cat1.show_seq as s2,rank()over(partition by cat2.product_id "
				+ "order by cat2.show_seq,cat1.show_seq ) as rank  " + "from ec_category_main cat1, ec_product_category cat2 "
				+ "where cat1.category_id = cat2.category_id  " + "and cat1.category_path = cat2.category_path "
				+ "and cat1.is_delete  = 'N'  " + "and cat1.category_path=? " + "order by cat2.product_id desc) "
				+ "where rank =1) b " + "where a.product_id = b.product_id and "
				+ "substr(a. v_uk,  (InStr(a.v_uk,'_')) +1 ) = b.category_id and " + "a.category_path=? "
				+ "and (a.is_delete = 'N' or a.is_delete = 'Q') " + "and a.product_id=? "
				+ "and to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') >= to_char(a.start_date,'yyyy-mm-dd hh24:mi:ss') "
				+ "and to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') < to_char(a.end_date,'yyyy-mm-dd hh24:mi:ss') ";

		List productList = jdbcTemplate.queryForList(checkSql, prodCatRC.getCategoryPath(), prodCatRC.getCategoryPath(),
				prodCatRC.getProductId());

		if (productList == null || productList.size() == 0) {
			return false;
		} else {
			Map productMap = (Map) productList.get(0);
			prodCatRC.setProductName((String) productMap.get("PRODUCT_NAME"));
			if (null != productMap.get("PROMOTE_NAME")) {
				prodCatRC.setPromoteName((String) productMap.get("PROMOTE_NAME"));
			}
			prodCatRC.setShelfPrice(((BigDecimal) productMap.get("SHELF_PRICE")).toString());
			prodCatRC.setListPrice(((BigDecimal) productMap.get("LIST_PRICE")).toString());

			return true;
		}
	}

	private String getTempZ(String categoryId) {
		long id = Long.parseLong(categoryId);
		Category category = (Category) baseManager.get(Category.class, id);
		String tempZ;
		if (Integer.valueOf(category.getLevel()) == 0) {
			tempZ = categoryId + "/%";
		} else if (category.getIsEndnode().equals("Y")) {
			tempZ = "%/" + categoryId;
		} else {
			tempZ = "%" + categoryId + "%";
		}

		return tempZ;
	}
}